import os,datetime

from utils.DBService import sqlquery

BANHEZHAN_SQL = """
SELECT
    a.departname,
    SUM(CASE WHEN baifenbi = '100' THEN 1 ELSE 0 END) AS baifenbi_100_count,    
    SUM(CASE WHEN baifenbi <> '100' THEN 1 ELSE 0 END) AS other_baifenbi_count,    
    COUNT(*) AS total_count 
FROM (  
    SELECT 
		a.departname, 
        a.baifenbi,
		c.org_type,
        COUNT(*) OVER() AS count_star  
    FROM WZRenWuDanZXQK  as a
		JOIN t_s_depart as c ON a.departid = c.id
    WHERE a.org_code LIKE (SELECT org_code FROM t_s_depart WHERE id = '{}') + '%' 
		{}
		and {}
		and c.depart_order not in (3,4)
) AS a
GROUP BY a.departname;"""

def shengchanrw_ovStatic(Args,user):
    print('Quest参数:\n\t',Args)
    depId = Args['depId']
    dates = Args['dates']
    depId = '\',\''.join(depId.split(','))

    if depId == '8a8ab0b246dc81120146dc8180ba0017'or \
            Args['depId'] == 'All':
        depId = '8a8ab0b246dc81120146dc8180ba0017'
        str_st = 'c.org_type = 9'
    elif depId == 'f6323b406b4fab1b016bacabdb9f01d3' or \
            depId == 'f6323b406b4fab1b016bacac674901d5'or \
            depId == 'f6323b406d19fc87016d1a5c43910016' or \
            depId == 'f6323b406d19fc87016d1a5b861b0014':
        str_st = 'c.org_type = 3'
    else:
        depId = '8a8ab0b246dc81120146dc8180ba0017'
        str_st = 'c.org_type = 9'

    dateWhere = 'AND CONVERT(nvarchar(10), kaipanriqi, 120) in  (\'{}\')'
    datearray = dates.split(',')
    if len(datearray) == 2:
        dateWhere = 'and CONVERT(nvarchar(10), kaipanriqi, 120)>=\'{}\' and CONVERT(nvarchar(10), kaipanriqi, 120)<=\'{}\''.format(
            datearray[0], datearray[1])
    else:
        dateWhere = dateWhere.format('\',\''.join(dates.split(',')))

    banhezhanSql = BANHEZHAN_SQL.format(depId,dateWhere,str_st)
    print('SQL:\n\t', banhezhanSql)
    banhezhanData = sqlquery(BANHEZHAN_SQL.format(depId,dateWhere,str_st))

    slData = []
    resdata = []
    if banhezhanData.__len__() == 0:
        marktitle = '当前时间没有数据'
        return resdata,slData,marktitle
    else:
        if len(datearray) == 2:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            end = datetime.datetime.strptime(datearray[1], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '到' + str(
                end.year) + '年' + str(end.month) + '月' + str(end.day) + '日' + '生产任务情况如下:'
        else:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '生产任务情况如下:'

    for banhezhan in banhezhanData:
        if os.environ.get("ENV_NAME")=='1':
            depName,TotalOrder,Completed,InProduction = banhezhan[0],round(banhezhan[3],2),round(banhezhan[1],2),round(banhezhan[2],2)
        else:
            depName,TotalOrder,Completed,InProduction = banhezhan[0],round(banhezhan[3],2),round(banhezhan[1],2),round(banhezhan[2],2)
        slData.append([
            depName,
            TotalOrder,
            Completed,
            InProduction
        ])
        resdata.append([
            depName,
            TotalOrder,
            Completed,
            InProduction
        ])
    print('Data:\n\t', slData)

    a_list = []
    b_list = []
    children = []
    for item in slData:
        station_name = item[0]
        a = item[0:]
        data = {
            'key': slData.index(item),
            'departname': a[0],
            'cjpanshu': a[1],
            'cjichaobL': a[2],
            'zjpanshu': a[3]
        }
        table_head = [
            {
                "title": '站点名称',
                "dataIndex": list(data.keys())[1],
                "key": list(data.keys())[1],
            },
            {
                "title": '任务单总数',
                "dataIndex": list(data.keys())[2],
                "key": list(data.keys())[2],
            },
            {
                "title": '生产中',
                "dataIndex": list(data.keys())[3],
                "key": list(data.keys())[3],
            },
            {
                "title": '已完成',
                "dataIndex": list(data.keys())[4],
                "key": list(data.keys())[4],
            }]
        if station_name.startswith("武黄1标"):
            a_list.append(data)
        else:
            b_list.append(data)
    if len(a_list)>0:
        childrenA_Data = {"children":[{"title": '武黄1标拌合站', "data": {"tableHead": table_head, "tableBody": a_list}}] ,"title": '武黄1标'}
        children.append(childrenA_Data)
    if len(b_list)>0:
        childrenB_Data = {"children":[{"title": '武黄2标拌合站', "data": {"tableHead": table_head, "tableBody": b_list}}] ,"title": '武黄2标'}
        children.append(childrenB_Data)

    result = [{"title": "武黄高速", "children": children}]

    print("result:\n\t",result)
    return resdata,result,marktitle;

functionConf = {
    "name": "shengchanrw_ovStatic",
    "description": "key point:生产任务。Query the number of production tasks based on the ID and time. eg: I'd like to check the production quantity of the standard 1 mixing station of Project A",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department IDs.Department IDs. 武黄1标是(f6323b406b4fab1b016bacabdb9f01d3).武黄2标是(f6323b406b4fab1b016bacac674901d5).If you ask project A, the default return is project A high speed"
            },
            "dates": {
                "type": "string",
                "description": "Date(s) of material production in yyyy-MM-dd format. Use comma-separated values (e.g., '2023-07-14,2023-07-15')."
            }
        },
        "required": [
            "depId",
            "dates"
        ]
    }
}
